/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ipcc.report.controller;

import com.ipcc.lazyLoading.LazyAbstractDataModel;
import com.ipcc.report.entity.CallTraffic;
import com.telsoft.applet.util.StringUtil;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import org.primefaces.model.LazyDataModel;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
@ViewScoped
public class CallTrafficReportController implements Serializable{
    private Date tuNgay;
    private Date denNgay;
    private String chitiet = "dd";
    private List<CallTraffic> items = new ArrayList<CallTraffic>();
    private LazyDataModel<CallTraffic> lazyDataModel;
    /**
     * Creates a new instance of CallTrafficReportController
     */
    public CallTrafficReportController() {
        items = new ArrayList<CallTraffic>();
    }

    public LazyDataModel<CallTraffic> getLazyDataModel() {
        return lazyDataModel;
    }

    public void setLazyDataModel(LazyDataModel<CallTraffic> lazyDataModel) {
        this.lazyDataModel = lazyDataModel;
    }

    public Date getTuNgay() {
        return tuNgay;
    }

    public void setTuNgay(Date tuNgay) {
        this.tuNgay = tuNgay;
    }

    public Date getDenNgay() {
        return denNgay;
    }

    public void setDenNgay(Date denNgay) {
        this.denNgay = denNgay;
    }

    public String getChitiet() {
        return chitiet;
    }

    public void setChitiet(String chitiet) {
        this.chitiet = chitiet;
    }

    public List<CallTraffic> getItems() {
        return items;
    }

    public void setItems(List<CallTraffic> items) {
        this.items = items;
    }
    
    
    // lay ve cau query theo condition
    public String getSQL(String condition){
        String sql = "select to_char(trunc(start_stamp,'"+chitiet+"'),'dd-mm-yyyy') ngay, sum(CEIL(duration/60)) tg, count(*) soCuocgoi, trunc((sum(CEIL(duration/60))/count(*)),3) tb"
                + " from fs_cdr "
                + condition
                + " group by trunc(start_stamp,'"+chitiet+"') "
                + " order by trunc(start_stamp,'"+chitiet+"') ";
        return sql;
    }
    
    //kiem tra input va tra ve query
    public String checkInput(){
        if (denNgay.compareTo(tuNgay) < 0) {               
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Từ ngày và đến ngày nhập không hợp lệ!"));
            return "";
        }
        String format = "dd-MM-yyyy";
        return getSQL(" where start_stamp >= TO_DATE('" + StringUtil.format(tuNgay, format) + "','dd-mm-yyyy') and start_stamp <= TO_DATE('" + StringUtil.format(denNgay, format) + "','dd-mm-yyyy') + 1");
    }
    public ArrayList<CallTraffic> searchByCondition(){
        ArrayList<CallTraffic> list = new ArrayList<CallTraffic>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String strSQL = "";
            strSQL = checkInput();
            if (strSQL != "") {
                cn = AppServer.getConnection();

                st = cn.createStatement();
                rs = st.executeQuery(strSQL);
                while (rs.next()) {
                    CallTraffic call = new CallTraffic();
                    call.setNgay(rs.getString(1));
                    call.setSoCuoc(Integer.parseInt(rs.getString(3)));
                    call.setThoiluong(Double.parseDouble(rs.getString(2)));
                    call.setTrungbinh(Double.parseDouble(rs.getString(4)));
                    list.add(call);
                }

                
                int i = list.size();
                if (i > 0) {
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(i + " kết quả được thống kê!"));
                } else {
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Không kết quả nào được thống kê!"));
                }
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: "+e.getMessage()));
        } finally {
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return null;
    }
    public String thongke(){
        items = searchByCondition();
        lazyDataModel = new LazyAbstractDataModel<CallTraffic>(items);        
        return "";
    }
}
